package com.ipartek.pruebas.bbdd;


import java.sql.*;

import com.ipartek.pruebas.bean.Alumno;
import com.ipartek.pruebas.enumeraciones.Rol;
import com.ipartek.pruebas.excepciones.AlumnoException;

public class MostrarAlumnosBDP1 {

	public static void consultas(){
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		
		try{
		System.out.println("Numero de personas con edad superior a 50");
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		con = DriverManager.getConnection("jdbc:odbc:prueba", "", "");
		st = con.createStatement();
		rs = st.executeQuery("SELECT COUNT(id) FROM ALUMNOS WHERE edad > 50;");
		while(rs.next()){
		System.out.println(rs.getString(1));
		}
		System.out.println("Numero de personas con edad inferior a 18");
		rs = st.executeQuery("SELECT COUNT(id) FROM ALUMNOS WHERE edad < 18;");
		while(rs.next()){
		System.out.println(rs.getString(1));	
		}
		
		System.out.println("Numero de personas con edad superior a 20 e inferior a 30");
		rs = st.executeQuery("SELECT COUNT(id) FROM ALUMNOS WHERE edad >= 20 AND edad <= 30;");
		while(rs.next()){
		System.out.println(rs.getString(1));	
		}
		
		System.out.println("Personas que contengan en su nombre AB");
		rs = st.executeQuery("SELECT COUNT(id) FROM ALUMNOS WHERE nombre LIKE '%ab%';");
		while(rs.next()){
		System.out.println(rs.getString(1));	
		}
		
		System.out.println("Personas que contengan en su nombre FR");
		rs = st.executeQuery("SELECT COUNT(id) FROM ALUMNOS WHERE nombre LIKE '%fr%';");
		while(rs.next()){
		System.out.println(rs.getString(1));
		}
		
		}catch(SQLException e){
			e.printStackTrace();
		}
		catch(ClassNotFoundException e){
			e.printStackTrace();
		}
		finally{
			try{
			if(rs != null){
			rs.close();
			}
			if(con != null){
				con.close();
			}
			if(st != null){
				st.close();
			}
			}
			catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
	
	public static void selectAlumnos(){
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;

		Alumno a = new Alumno();
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			con = DriverManager.getConnection("jdbc:odbc:prueba", "", "");
			st = con.createStatement();
			String sql = "SELECT * FROM ALUMNOS;";
			rs = st.executeQuery(sql);
			while(rs.next()){
				try{
				a.setNombre(rs.getString(3));
				a.setApellido1(rs.getString(4));
				a.setApellido2(rs.getString(5));
				a.setDni(rs.getString(2));
				a.setEdad(rs.getInt(6));
				a.setId(rs.getInt(1));
				a.setId_curso(rs.getInt(9));
				int comp = rs.getInt(7);
				if(comp == 0){
				a.setRol(Rol.TRABAJADOR);
				}
				else if(comp == 1){
				a.setRol(Rol.CLIENTE);	
				}
				else{
				a.setRol(Rol.JEFE);	
				}
				a.setEmail(rs.getString(8));
				System.out.println(a.toString());
				}catch(AlumnoException e){
					e.printStackTrace();
				}
			}
			
		}catch(SQLException e){
			e.printStackTrace();
		}
		catch(ClassNotFoundException e){
			e.printStackTrace();
		}
		finally{
			try{
			if(rs != null){
			rs.close();
			}
			if(con != null){
				con.close();
			}
			if(st != null){
				st.close();
			}
			}
			catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		//selectAlumnos();
		consultas();

	}

}
